package com.seeyon.apps.brwh.service;

import com.alibaba.fastjson.JSONObject;
import com.seeyon.apps.brwh.abstraction.ExcelUtil;
import com.seeyon.apps.brwh.kit.HttpKit;
import com.seeyon.apps.brwh.kit.StrKit;
import com.seeyon.ctp.common.AppContext;
import com.seeyon.ctp.common.authenticate.domain.User;
import com.seeyon.ctp.common.constants.ApplicationCategoryEnum;
import com.seeyon.ctp.common.ctpenumnew.manager.EnumManager;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.common.filemanager.Constants;
import com.seeyon.ctp.common.filemanager.manager.AttachmentManager;
import com.seeyon.ctp.common.filemanager.manager.FileManager;
import com.seeyon.ctp.common.log.CtpLogFactory;
import com.seeyon.ctp.common.po.ctpenumnew.CtpEnumItem;
import com.seeyon.ctp.common.po.filemanager.Attachment;
import com.seeyon.ctp.common.po.filemanager.V3XFile;
import com.seeyon.ctp.organization.bo.V3xOrgDepartment;
import com.seeyon.ctp.organization.bo.V3xOrgEntity;
import com.seeyon.ctp.organization.bo.V3xOrgMember;
import com.seeyon.ctp.organization.bo.V3xOrgUnit;
import com.seeyon.ctp.organization.manager.OrgManager;
import com.seeyon.ctp.organization.po.OrgMember;
import com.seeyon.ctp.rest.resources.CustomNewTaskResources;
import com.seeyon.ctp.util.UUIDLong;
import org.apache.commons.logging.Log;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.text.ParseException;
import java.util.*;

public class ExportMxTableDataService  extends ExcelUtil {
    private FileManager fileManager = (FileManager)AppContext.getBean("fileManager");
    private EnumManager enumManagerNew = (EnumManager) AppContext.getBean("enumManagerNew");
    private OrgManager orgManager = (OrgManager)AppContext.getBean("orgManager");
    private static final Log log = CtpLogFactory.getLog(ExportMxTableDataService.class);
    //是否下载模板
    private Boolean temp = true;

    //导出数据
    private List<ArrayList<String>> dowData = null;

    //导出表头
    private List<String> heads = null;

    //导出表头
    private HashMap<String,String[]> xlData = null;

    //备注数据
    private String remarkData = "";


    /**
     * 准备下载excle数据
     * @throws Exception
     */
    public String cacheExcelData(Map<String, Object> data){
        try {
            //生成随机数
            String uuid = UUID.randomUUID().toString();
            AppContext.putCache(uuid,data);
            return uuid;
        }catch (Exception e) {
            log.info("存入缓存数据异常："+e);
            return "-1";
        }
    }


    /**
     * 下载数据
     * @param response
     * @param uuid
     */
    public void ExportMxTableData(HttpServletResponse response,String uuid){
        Map<String, Object> param = (Map<String, Object>)AppContext.getCache(uuid);
        //获取导出的数据
        List<Map<String, Object>> dataList = (List<Map<String, Object>>)param.get("subDataList");
        //获取下拉数据
        HashMap<String,ArrayList<String>> xlDataMap = (HashMap<String,ArrayList<String>>)param.get("xlDataMap");
        xlData = new HashMap<>();
        for (String s : xlDataMap.keySet()) {
            ArrayList<String> strings = xlDataMap.get(s);
            String[] strings1 = new String[strings.size()];
            for(int i = 0; i < strings.size(); i++){
                //将枚举转换编码
                String s1 = strings.get(i);
                String[] split = s1.split("【");
                String enumId = split[1].replace("】", "");
                String enumCode ="";
                try {
                    CtpEnumItem ctpEnumItem = enumManagerNew.getCtpEnumItem(Long.parseLong(enumId));
                    enumCode = ctpEnumItem.getEnumItemCode();
                } catch (BusinessException e) {
                    log.info("自定义导出模板数据枚举转换异常："+e);
                }
                s1 = split[0]+"【"+enumCode+"】";
                strings1[i] = s1;
            }
            xlData.put(s,strings1);
        }
        //获取备注信息
        remarkData = StrKit.str(param.get("remarkValue"));

        //删除缓存数据
        AppContext.removeCache(uuid);
        dowData = new ArrayList<>();
        heads = new ArrayList<>();
        temp = false;
        String exclelName = "销售报价";
        //赋值表头
        Set<String> strings = dataList.get(0).keySet();
        for (String s : strings) {
            heads.add(s);
        }
        //组装导出数据类容
        dataList.forEach(d->{
            ArrayList<String> list = new ArrayList<>();
            heads.forEach(h->{
                if("recordId".equals(h)){
                    list.add(StrKit.str(d.get("recordId")));
                }else{
                    Map<String,Object> dataMap = (Map<String,Object>)d.get(h);
                    String inputType = StrKit.str(dataMap.get("inputType"));
                    if(inputType.equals("image")){
                        ArrayList<HashMap<String, Object>> valueList = (ArrayList<HashMap<String, Object>>)dataMap.get("value");
                        String s = "";
                        if(valueList.size() != 0){
                            String url = StrKit.str(valueList.get(0).get("url"));
                            String[] split = url.split("fileId=");
                            String[] split1 = split[1].split("&");
                            s = split1[0] + "_image";
                        }
                        list.add(s);
                    }else{
                        String type = StrKit.str(dataMap.get("type"));
                        String showValue = StrKit.str(dataMap.get("showValue"));
                        String auth = StrKit.str(dataMap.get("auth"));
                        if(!"hide".equals(auth)){
                            if(!showValue.equals("")){
                                if(type.equals("select")){
                                    String[] split = showValue.split("【");
                                    String enumId = split[1].replace("】", "");
                                    try {
                                        CtpEnumItem ctpEnumItem = enumManagerNew.getCtpEnumItem(Long.parseLong(enumId));
                                        showValue = split[0]+"【"+ctpEnumItem.getEnumItemCode()+"】";
                                    } catch (BusinessException e) {
                                        log.info("自定义导出模板数据枚举转换异常2："+e);
                                    }

                                }
                            }
                        }
                        list.add(showValue);
                    }
                }
            });
            dowData.add(list);
        });
        //获取数据
        try {
            downLoadExcel(exclelName+"数据.xls", response);
            dowData = null;
            heads = null;
            temp = true;
            xlData = null;
            remarkData = null;
        } catch (Exception e) {
            log.info("下载数据异常："+e);
            dowData = null;
            heads = null;
            temp = true;
            xlData = null;
            remarkData = null;
            return;
        }

    }



    /**
     * 导入Excel数据
     * @param inputStream
     * @param fileName
     * @return
     */
    public HashMap<String, Object> impotExcleData(InputStream inputStream, String fileName) throws IOException, BusinessException {
        HashMap<String, Object> resultMap = new HashMap<>();
        ArrayList<String> err = new ArrayList();

        InputStream newis = inputStream;
        // 将数据流转成集合
        Map<String, Object> stringObjectMap = excelToList(newis, fileName);
        List<String[]> strings = (List<String[]>)stringObjectMap.get("dataList");
        //删除首行
        strings.remove(0);
        //判断上传文件数据是否为空
        if (strings.isEmpty()) {
            err.add("选择的文件内容为空，请填写内容后再试~");
            resultMap.put("status", 0);
            resultMap.put("data", err);
            resultMap.put("msg", err);
            return resultMap;
        }
        //获取Excel图片信息
        HashMap<String, HashMap<String, Object>> picInfo = (HashMap<String, HashMap<String, Object>>)stringObjectMap.get("imgList");
        if(picInfo != null && !picInfo.keySet().isEmpty()){
            for (String key : picInfo.keySet()) {
                HashMap<String, Object> map = picInfo.get(key);
                byte[] bytes = (byte[]) map.get("imgData");
                String imgName = StrKit.str(map.get("imgName"));
                InputStream is = new ByteArrayInputStream(bytes);
                //上传图片
                List<Attachment> atts = new ArrayList();
                ApplicationCategoryEnum category = ApplicationCategoryEnum.global;
                Constants.ATTACHMENT_TYPE type = Constants.ATTACHMENT_TYPE.FILE;
                V3XFile v3XFile = fileManager.save(is, category, imgName, new Date(), false);
                fileManager.save(v3XFile);
                Attachment att = new Attachment(v3XFile, category, type);
                att.setSubReference(UUIDLong.longUUID());
                atts.add(att);
                map.put("imgAtts",atts);
            }
        }

        //表头
        String[] titleNames = strings.get(0);
        //删除表头
        strings.remove(0);
        //组装图片数据
        List<HashMap<String,Object>> excelData = new ArrayList<>();
        for (int j = 0; j < strings.size(); j++) {
            String[] string = strings.get(j);
            HashMap<String, Object> map = new HashMap<>();
            for (int i = 0; i < titleNames.length; i++) {
                String value = "";
                if(i<=(string.length-1)){
                    value = StrKit.str(string[i]);
                }
                String titleName = titleNames[i];
                String titleType = "";
                if(titleName.contains("【")){
                    String[] split = titleName.split("【");
                    titleType = split[1].replaceAll("】", "");
                }
                if(titleName.contains("图片")){
                    String key = (j+2)+"行"+i+"列";
                    HashMap<String, Object> map1 = picInfo.get(key);
                    if(map1 != null){
                        List<Attachment> atts = (List<Attachment>) map1.get("imgAtts");
                        HashMap<String, Object> map2 = new HashMap<>();
                        Long subReference = atts.get(0).getSubReference();
                        map2.put("value", subReference.toString());
                        map2.put("display", subReference.toString());
                        map2.put("placeHolder","");
                        map2.put("auth", "browse");
                        map2.put("atts", atts);
                        map.put(titleName, map2);
                    }else{
                        String typeData = getTypeData(titleType, value);
                        if(value.contains("【")){
                            value = value.split("【")[0];
                        }
                        if(titleName.contains("【")){
                            titleName = titleName.split("【")[0];
                        }
                        HashMap<String, Object> map2 = new HashMap<>();
                        map2.put("value", typeData);
                        map2.put("display", value);
                        map2.put("placeHolder","");
                        map2.put("auth", "");
                        map2.put("atts", "");
                        map.put(titleName, map2);
                    }
                }else{
                    String typeData = getTypeData(titleType, value);
                    if(value.contains("【")){
                        value = value.split("【")[0];
                    }
                    if(titleName.contains("【")){
                        titleName = titleName.split("【")[0];
                    }
                    HashMap<String, Object> map2 = new HashMap<>();
                    map2.put("value", typeData);
                    map2.put("display", value);
                    map2.put("placeHolder","");
                    map2.put("auth", "");
                    map2.put("atts", "");
                    map.put(titleName, map2);
                }
            }
            map.put("uuid", UUIDLong.longUUID());
            excelData.add(map);
        }
        resultMap.put("status", 1);
        resultMap.put("data", JSONObject.toJSONString(excelData));
        resultMap.put("msg", err.add("导入数据成功"));
        return resultMap;
    }

    /**
     * 导出Excel模板
     * @param uuid  获取数据随机码
     * @throws Exception
     */
    public void downLoadExcelTemp(String uuid,HttpServletResponse response){
        List<Map<String,Object>> dataList = (List<Map<String,Object>>)AppContext.getCache(uuid);
        Map<String,Object> dataMap = dataList.get(0);
        String excelName = StrKit.str(dataMap.get("excelName"));
        String heder = StrKit.str(dataMap.get("heder"));
        String remarkValue = StrKit.str(dataMap.get("remarkValue"));
        HashMap<String,ArrayList<String>> xlDataMap = (HashMap<String,ArrayList<String>>)dataMap.get("xlDataMap");
        xlData = new HashMap<>();
        for (String s : xlDataMap.keySet()) {
            ArrayList<String> strings = xlDataMap.get(s);
            String[] strings1 = new String[strings.size()];
            for(int i = 0; i < strings.size(); i++){
                strings1[i] = strings.get(i);
            }
            xlData.put(s,strings1);
        }

        //删除缓存数据
        AppContext.removeCache(uuid);

        heads = new ArrayList<>();
        remarkData = remarkValue;
        temp = true;
        for (String s : heder.split(",")) {
            heads.add(s);
        }
        //获取数据
        try {
            downLoadExcel(excelName+"模板.xls", response);
        } catch (Exception e) {
            log.info("下载Excel模板异常："+e);
            heads = null;
            remarkData = null;
            return;
        }
        heads = null;
        remarkData = null;
        xlData = null;
    }


    @Override
    public HSSFWorkbook wb() {
        //生成Excel数据
        HSSFWorkbook wb = new HSSFWorkbook();
        //设置字体加粗样式
        CellStyle headStyle = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBold(true);
        font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        headStyle.setFont(font);

        if(temp){
            //获取Excel表头字段
            HSSFSheet sheet = wb.createSheet();
            sheet.setDefaultColumnWidth(14);
            sheet.setColumnWidth(1, 16 * 256 + 184);
            //创建备注行
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, heads.size() - 1);
            sheet.addMergedRegion(cellRangeAddress);
            HSSFRow remarkRow = sheet.createRow(0);
            remarkRow.setHeight((short) 1000);
            remarkRow.setRowStyle(headStyle);
            //设置备注信息
            HSSFCell cell1 = remarkRow.createCell(0);
            cell1.setCellValue(remarkData);
            //获取下拉数据
            HashMap<String,String[]> xlDataMap = xlData;

            HSSFRow headRow = sheet.createRow(1);
            headRow.setHeight((short) 500);
            headRow.setRowStyle(headStyle);
            for (int s = 0; s < heads.size(); s++) {
                HSSFCell cell = headRow.createCell(s);
                cell.setCellValue(heads.get(s));
                if(xlData != null && !xlData.isEmpty()){
                    if(heads.get(s).contains("【下拉】")){
                        String s1 = heads.get(s);
                        String[] strings = xlDataMap.get(s1);
                        ExcelUtil.addExcelXlData(sheet,2,s,11,s,strings);
                    }
                }
            }
            //添加隐藏列
//            sheet.setColumnHidden(heads.size()-1,true);

        }else{
            if(dowData != null){
                HSSFSheet sheet = wb.createSheet();
                sheet.setDefaultColumnWidth(14);
                sheet.setColumnWidth(1, 16 * 256 + 184);
                //创建备注行
                CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, heads.size() - 1);
                sheet.addMergedRegion(cellRangeAddress);
                HSSFRow remarkRow = sheet.createRow(0);
                remarkRow.setHeight((short) 1000);
                remarkRow.setRowStyle(headStyle);
                //设置备注信息
                HSSFCell cell1 = remarkRow.createCell(0);
                cell1.setCellValue(remarkData);
                //获取下拉数据
                HashMap<String,String[]> xlDataMap = xlData;

                HSSFRow headRow = sheet.createRow(1);
                headRow.setHeight((short) 500);
                headRow.setRowStyle(headStyle);
                //创建excel画笔
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                //生成表头字段信息
                for(int j = 0; j < heads.size(); j++){
                    HSSFCell cell = headRow.createCell(j);
                    cell.setCellValue(heads.get(j));

                    if(xlData != null && !xlData.isEmpty()){
                        if(heads.get(j).contains("【下拉】")){
                            String s1 = heads.get(j);
                            String[] strings = xlDataMap.get(s1);
                            int lastRowsIndex = 13;
                            if(dowData.size()>0){
                                lastRowsIndex = dowData.size()+2;
                            }
                            ExcelUtil.addExcelXlData(sheet,2,j,lastRowsIndex,j,strings);
                        }
                    }
                }
                //添加隐藏列
                sheet.setColumnHidden(heads.size()-1,true);

                //生成Excel主体数据
                for(int i=0;i<dowData.size();i++){
                    HSSFRow bodyRow = sheet.createRow(i+2);
                    bodyRow.setHeight((short)1000);
                    ArrayList<String> strings = dowData.get(i);
                    for(int j = 0;j<strings.size();j++){
                        String s = strings.get(j);
                        //判断是否是图片
                        if(s.contains("_image")){
                            s = s.replace("_image","");
                            drawPictureInfoExcel(wb,patriarch,i+2,j,s);
                        }else{
                            HSSFCell cell = bodyRow.createCell(j);
                            cell.setCellValue(strings.get(j));
                        }
                    }
                }
            }
        }
        return wb;
    }

    /**
     * 往excel中插入图片
     * @param wb
     * @param patriarch
     * @param rowIndex
     */
    private void drawPictureInfoExcel(HSSFWorkbook wb, HSSFPatriarch patriarch, int rowIndex,int cellIndex,String fileId){
        //rowIndex代表当前行
        try {
            //得到图片的二进制数据，以二进制封装得到数据，具有通用性
            byte[] data = readInputStream(fileId);
            //anchor主要用于设置图片的属性
            //dx1起始单元格的x偏移量
            //dy1起始单元格的y偏移量
            //dx2终止单元格的x偏移量
            //dy2终止单元格的y偏移量
            //col1起始单元格的列号,从0开始
            //row1起始单元格的行号,从0开始
            //col2终止单元格的列号,从0开始
            //row2终止单元格的行号,从0开始
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 250,(short) cellIndex, rowIndex, (short) cellIndex, rowIndex);
            //Sets the anchor type （图片在单元格的位置）
            //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_PNG));
        } catch (Exception e) {
            log.info("往Excel中插入图片异常："+e);
        }
    }




    /**
     * 输出图片二进制流
     * @return
     * @throws Exception
     */
    private byte[] readInputStream(String fileId) throws Exception{
        InputStream inStream = fileManager.getFileInputStream(Long.parseLong(fileId));
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while( (len=inStream.read(buffer)) != -1 ){
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();
    }


    /**
     * 根据类型转换数据
     * @param type
     * @param value
     * @return
     */
    private String getTypeData(String type,String value) throws BusinessException {
        String typeData = "";
        switch (type){
            case "下拉":
                if(value.contains("【")){
                    String[] split = value.split("【");
                    String s = split[1].replaceAll("】", "");
                    CtpEnumItem itemByCode = enumManagerNew.getItemByCode(s);
                    typeData = itemByCode.getId().toString();
                }else{
                    typeData = value;
                }
                break;
            case "选部门":
                if(value.contains("【")){
                    String[] split = value.split("【");
                    String s = split[1].replaceAll("】", "");
                    List<V3xOrgEntity> v3xOrgEntities = orgManager.getEntityList(V3xOrgDepartment.class.getSimpleName(),
                            "code", s, null, false, true);
                    if(!v3xOrgEntities.isEmpty()){
                        typeData = v3xOrgEntities.get(0).getId().toString();
                    }else{
                        typeData = value;
                    }
                }else{
                    typeData = value;
                }
                break;
            case "选人员":
                if(value.contains("【")){
                    String[] split = value.split("【");
                    String s = split[1].replaceAll("】", "");
                    List<V3xOrgEntity> v3xOrgEntities = orgManager.getEntityList(V3xOrgMember.class.getSimpleName(),
                            "code", s, null, false, true);
                    if(!v3xOrgEntities.isEmpty()){
                        typeData = v3xOrgEntities.get(0).getId().toString();
                    }else{
                        typeData = value;
                    }
                }else{
                    typeData = value;
                }
                break;
            case "选单位":
                if(value.contains("【")){
                    String[] split = value.split("【");
                    String s = split[1].replaceAll("】", "");
                    List<V3xOrgEntity> v3xOrgEntities = orgManager.getEntityList(V3xOrgUnit.class.getSimpleName(),
                            "code", s, null, false, true);
                    if(!v3xOrgEntities.isEmpty()){
                        typeData = v3xOrgEntities.get(0).getId().toString();
                    }else{
                        typeData = value;
                    }
                }else{
                    typeData = value;
                }
                break;
            default:
                typeData = value;
        }
        return typeData;
    }
}
